<?php
// pdo_access.inc.php
/*
 * Copyright (C) 2004-2007 Manuel Kägi, kaegi(at)gmx(dot)ch
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or (at
 * your option) any later version.
 *
 * This program is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */


/**
*    GENERAL NOTES
* ============================
*
*    1. This class requires methods form stdfuncs.inc.php (can be found at www.corvent.ch)
*        stdfuncs.inc.php will be looked up in a folder "core" going off from any php-include path.
*        If you dont want a subfolder for stdfuncs.inc.php just change the require_once argument.
*
*    2. Generrally you can access all DBMS for which a PDO-driver is available, but tested is only MySQL and SQLite
*        The class-Variable "specEscFunction" specifies the correct escaping functions according to the DBMS
*
*    3. The Values you get from getSQLResultsAsArray are formatted in a way to use them as data for all
*        tableXXXX in the class arrayfunc in stdfuncs.inc.php (tableSort, tableGroup etc..)
*
*    4. To inspect the results from any getSQLReusltXXXX() function use debug::show(), defined in stdfuncs.inc.php
*
*    5. All SQL-Staements will measure their time using the debug::startMessung and stopMessung methods
*        if you are intrested in these results you can print them to the standard Output using
*        debug::showLaufzeit ("Laufzeit" means "runtime" in german), You can do this after all your
*        Db-Operations are done, you will get a nicely formatted overview over all SQL-Statements and their 
*        runtime consumption.
*        This measurings need very little time itself, if you eigther want to switch them off just
*        remove all "debug::startMessung" and "debug::stopMessung" commands 
*    
*/
require_once("stdfuncs.inc.php");

class pdo_access {
    
    var $db;
    var $debugMode        = false;
    
    var $dsn;
    var $user;
    var $pw;
    
    var $escapingFunction = "addslashes";
    var $specEscFunction  = array('sqlite'=>'sqlite_escape_string', 'sqlite2'=>'sqlite_escape_string');
    var $flConnected      = false;
    
    /** 
    *    Instantiates a pdo_access Object and connects to the specified Database Server 
    *        
    *    @param string $dbServerSpec the dsn of the database to connect with 
    *        {dbType}:{dsn}
    *    @param string $user the username (if you use a password protected database)
    *    @param szring $pw: the password to use
    */
    function pdo_access($dbServerSpec, $user="", $pw="") {
        $this->dsn  = $dbServerSpec;
        $this->user = $user;
        $this->pw   = $pw;
    }
    
    /** connect
    */
    function connect() {
        $Result = false;
        try { 
            // $this->db = new PDO($dbServerSpec, $user, $pw, array(PDO::ATTR_PERSISTENT => true));
            $this->db = new PDO( $this->dsn, $this->user, $this->pw );
            $this->db->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);    
            $Result = false;
        }
        catch (PDOException $e) {
            print("pdo_access: Connection failed: ".$e->getMessage()."\n<br>");
            print("DSN = '".$this->dsn."'\n<br>");
            error_log( "pdo_access: Connection failed: ".$e->getMessage()."\n" );
            error_log("DSN = '".$this->dsn."'\n");
        }
        
        $this->flConnected = $Result;
        return $Result;
    }
    
    
    //    SELECT SQL's
    // =======================
    
    /**
    *    Performs a SELECT query and returns the result as an array of hashtables representing the records
    *
    *    @param string $sql, the SQL to perform (e.g. "SELECT * FROM table")
    *    @param string $keyCol - OPTIONAL - if set the value of the specified column will be used as index in the array of the record-Haschtables, 
    *            typically "id" OR "username", this column should be unique over the whole result of the query, 
    *            otherwise you wont get all records, the later ones with the same value in the keyCol will
    *            overwrite the earlier ones
    *
    *            If $keyCol is not set the indizes of the records will go from 0 to the queryresult's length - 1
    *
    *    @return array an array of haschtables representing the result of the query
    */
    function getSQLResultAsArray($sql, $keyCol = "") {
        //debug::startMessung("getSQLResultAsArray");
        error_log( $sql );
        $result = $this->select( $sql, PDO::FETCH_ASSOC );
        
        //error_log( $keyCol );
        //error_log( var_export( $result, true ) );
        // debug::show($result, "result");
        
        if ( $keyCol != "" ) {
            $hashedResult = array();
            foreach ( $result as $row ) {
                $key = $row[ $keyCol ];
                unset( $row[ $keyCol ] );
                $hashedResult[$key] = $row;
            }
            
            $result = $hashedResult;
        }
        
        //error_log( var_export( $result ) );
        //debug::stopMessung("getSQLResultAsArray");
        return $result;
    }    

    /**
    *    Performs a SELECT query and returns the result as a Hashtable
    *
    *    The values of the first column will bey used as Key of the Hashtable
    *    If the COLUMN-Count is 2, the value of second Column will be used as value, this is the intended way to use this method
    *    If the COLUMN-Count is 1, the value will always be the same as the key
    *    If the COLUMN-Count is greater than 2 the value will be the whole record (as a hashtable)
    *
    *    @param string $sql, the SQL-Query to perform, typically "SELECT id, name FROM users";
    *
    *    @return array the result as hashtable
    */
    function getSQLResultAsHashtable($sql) {
        //debug::startMessung("getSQLResultAsHashtable");
        $colCount = 0;
        $result = array(); 
		//echo $sql;
        $numericArray = $this->select($sql, PDO::FETCH_NUM);
        if (count($numericArray > 0)) $colCount = count($numericArray[0]);
        foreach ($numericArray as $row) {
            $key = $row[0];

            if ($colCount == 2) $value = $row[1];
            if ($colCount > 2) $value = $row;
            if ($colCount == 1) $value = $key;

            $result[$key] = $value;
        }
        //debug::stopMessung("getSQLResultAsHashtable");    
        return $result;
    }    
    
    /**
    *    Performs a SELECT query and returns values found in the firs Column as numeric array
    *
    *    @param string $sql, the SQL-Query to perform, typically "SELECT name FROM users";
    *
    *    @return array, the first-col values
    */
    function getSQLResultFirstCol($sql) {
        //debug::startMessung("getSQLResultFirstCol");
        $result = array();
        $numericArray = $this->select($sql, PDO::FETCH_NUM);
        foreach ($numericArray as $row) {
            $result[] = $row[0];
        }
        //debug::stopMessung("getSQLResultFirstCol");    
        return $result;
    }

    /**
    *    Performs a SELECT query and returns the found (first) record as hashtable
    *    The query itself should be designed so that its result will countain only one record
    *
    *    @param string $sql, the SQL-Query to perform, typically "SELECT * FROM users WHERE id='12'";
    *    @param boolean $allowMultipleMatch, - OPTIONAL - if the method schould ignore if there are more than one record in the querys result
    *        - true => if a query brings more than one record, the first one is returned
    *        - false => if a query brings more than one record, boolean false is returned
    *            The intention is to indicate possible inconsitency of the database
    *            This behavior is default
    *
    *    @return array, the record as hashtable
    */
    function getSQLResultFirstRow($sql, $allowMultipleMatch = false) {
		//echo $sql;
		//error_log( $sql );
        //debug::startMessung("getSQLResultFirstRow");
        $assocArray = $this->select($sql, PDO::FETCH_ASSOC);
        //debug::stopMessung("getSQLResultFirstRow");    
        if (count($assocArray) == 1) return $assocArray[0];
        if (count($assocArray) > 1 && $allowMultipleMatch) $assocArray[0];
        return false;
    }

    /**
    *    Performs a SELECT query and returns the first cell of  found (first) record.
    *
    *    @param string $sql, the SQL-Query to perform, typically "SELECT name FROM users WHERE id='12'";
    *    @param boolean $allowMultipleMatch, - OPTIONAL - if the method schould ignore if there are more than one record in the querys result
    *        - true => if a query brings more than one record, the first cell of the first record is returned
    *        - false => if a query brings more than one record, boolean false is returned
    *            The intention is to indicate possible inconsitency of the database
    *            This behavior is default
    *
    *    @return string, the found value
    *
    *
    */
    function getSQLResultFirstCell($sql, $allowMultipleMatch = false) {
        //debug::startMessung("getSQLResultFirstCell");
        $numericArray = $this->select($sql, PDO::FETCH_NUM);
        //debug::stopMessung("getSQLResultFirstCell");    
                
        if (count($numericArray) == 1) return $numericArray[0][0];
        if (count($numericArray) > 1 && $allowMultipleMatch) $numericArray[0][0];
        return false;
    }
    
    
    //    COUNT / DISTINCT SQL's
    // ===================================

    /**
    *    Counts all records of a table that fulfill the given condition
    *
    *    @param string $table, the table to query
    *    @param string $cond - OPTIONAL - the condition the records to count must fulfill
    *        - default: no condition, all records of the table will be counted
    *
    *    countRecords($table, $cond) 
    *        is a shortcut for
    *    getSQLResultFirstCell("SELECT COUNT(*) FROM $table WHERE $cond");
    *    
    *    @return int the number of records found
    */
    function countRecords($table, $cond = "") {
        $sql = "SELECT COUNT(*) FROM $table";
        if ($cond != "") {
            $sql.=" WHERE $cond";
        }
        
        error_log( $sql );
        
        $count = $this->getSQLResultFirstCell($sql);
        if (! $count === false) {
            $count = (int) $count;
        }
        
        return $count;
    }

    /**
    *    Returns all different values found in a column of a table
    *    Optionally a condition that the records must fulfill can be given
    *
    *    @param string $table, the table to query
    *    @param string $column, the column whose distinct values interest
    *    @param string $cond - OPTIONAL - the condition the records to count must fulfill
    *
    *    getDistinctValues($table, $column, $cond) 
    *        is a shortcut for
    *    getSQLResultFirstCol("SELECT DISTINCT($column) FROM $table WHERE $cond");
    */
    function getDistinctValues($table, $column, $cond = "") {
        $sql = "SELECT DISTINCT($column) FROM $table";
        if ($cond != "") $sql.=" WHERE $cond";
        return $this->getSQLResultFirstCol($sql);
    }

    /**
    *    Counts all different values found in a column of a table
    *    Optionally a condition that the records must fulfill can be given
    *
    *    @param string $table, the table to query
    *    @param string $column, the column whose distinct values interest
    *    @param string $cond - OPTIONAL - the condition the records to count must fulfill
    *
    *    countDistinctValues($table, $column, $cond) 
    *        is a shortcut for
    *    getSQLResultFirstCell("SELECT COUNT(DISTINCT($column)) FROM $table WHERE $cond");
    */
    function countDistinctValues($table, $column, $cond = "") {
        $sql = "SELECT COUNT(DISTINCT($column)) FROM $table";
        if ($cond != "") $sql.=" WHERE $cond";
        $count = $this->getSQLResultFirstCell($sql);
        if (! $count === false) $count = (int) $count;
        return $count;
    }



    //    INSERT / UPDATE / DELETE
    // ===================================

    /**
    *    Inserts values, given as hashtable into the given table
    *    The keys of the hashtable MUST represten the columnnames of the table
    *    Keys that do not exist as column in the table will lead to an SQL-Error!
    *    The values will be escaped according to the used databse system (actully the known DBMS ar MySQL and SQLite
    *    Values given in the third argument (also a hashtable) will not be escaped
    *    
    *    @param string $tablename, the name of the table to insert the values into
    *    @param array $valueArray, the values to inser as hashtable (associative array) 
    *    @param array $functions, - OPTIONAL - the unescaped values to insert as hashtable
    *        e.g. $functions = array('pw' => "PASSWORD('secret')");
    *        if this would be in the $valueArray argument, "PASSWORD('secret')" would be inserted what hardly will be your intention
    *
    *    @return int the inserted id if an auto_increment column is in the table
    *        int the number of inserted rows if no auto_increment col available
    *        boolean false on error
    */
    function insertValues( $tablename, $valueArray, $functions = array(), $noQuote = array() ) {
        //debug::startMessung("insertValues");
        $clist = ""; 
        $vlist = "";
        
        if ( is_array( $valueArray ) ) {
            foreach ( $valueArray as $col => $value ) {
                $value = $this->escapeForDbQuery( $value );
                if ( is_array( $noQuote ) && array_search( $col, $noQuote ) === false ) {
                    $functions[ $col ] = "'$value'";
                } 
                else {
                    $functions[ $col ] = $value;
                }
                
                if ( is_null( $value ) ) {
                    $functions[$col] = "NULL";
                }
            }
        }
 
        if ( is_array( $functions ) ) {
            foreach ( $functions as $col => $value ) {
                $clist.= "$col, ";
                $vlist.= "$value, ";
            }
        }
        
        $clist = substr($clist, 0, -2);
        $vlist = substr($vlist, 0, -2);
        
        $sql = "INSERT IGNORE INTO $tablename ($clist) VALUES ($vlist)";
        //debug::stopMessung("insertValues");
        //echo $sql;
        error_log( $sql );
        return $this->insert( $sql, $tablename );
    }

    /**
    *    Updates values, given as hashtable in the given table in every record that fulfills the given condition
    *    The keys of the hashtable MUST represten the columnnames of the table
    *    Keys that do not exist as column in the table will lead to an SQL-Error!
    *    The values will be escaped according to the used databse system (actully the known DBMS ar MySQL and SQLite
    *    Values given in the forth argument (also a hashtable) will not be escaped
    *    
    *    @param string $tablename, the name of the table to updete the values in
    *    @param string $condition, coundtion the records must fulfill to be updated (e.g. "id=12")
    *    @param array $valueArray, new values as hashtable (associative array) 
    *    @param array $functions, - OPTIONAL - the unescaped new values as hashtable
    *        e.g. $functions = array('pw' => "PASSWORD('secret')");
    *        if this would be in the $valueArray argument, the "pw" field would be updated to "PASSWORD('secret')" what hardly will be your intention
    *
    *    @return int the number of updated records
    *        boolean false on error
    *        
    *    NOTES:
    *        - use "===" to check for failures, integer 0 would mean: no SQL-Error, but no record found 
    *            that fulfills the given condition
    *        - if the condition is empty nothing will be done to avoid that you update a whole 
    *            table to the same values by a mistake. If you want to do so give something like
    *            "true" or "1 = 1" as condition
    */
    function updateValues( $tablename, $condition, $valueArray, $noQuote = array(), $functions = array() ) {    
        //debug::startMessung("updateValues");
        
        $list = "";
        if ( is_array( $condition ) ) {
            foreach ( $condition as $col => $value ) {
                $value = $this->escapeForDbQuery( $value );
                if ( is_array( $noQuote ) && array_search( $col, $noQuote ) === false ) {
                    $c[] = "$col='$value'";
                } 
                else {
                    $c[] = "$col=$value";
                }
            }
            
            $condition = implode(" AND ", $c);
        }

        //debug::startMessung($tablename." WHERE ".$condition);
        if ( is_array( $valueArray ) ) {
            foreach ( $valueArray as $col => $value ) {
                $functions[$col] = "'".$this->escapeForDbQuery($value)."'";
                if ( is_null( $value ) ) 
                    $functions[$col] = "NULL";
            }
        }
        
        if ( is_array( $functions ) ) {
            foreach ( $functions as $col => $value ) {
                $list.= "$col=$value, ";
            }
        }
        
        $list = substr($list, 0, -2);
        $retVal = false;
        
        if ( $condition != "" ) { // Damit nicht die ganze Tabelle verholzt werden kann!!
            $sql = "UPDATE $tablename SET $list WHERE $condition";
            //echo $sql;
            error_log( $sql );
            $retVal = $this->execute($sql);
        }

        //debug::stopMessung($tablename." WHERE ".$condition);
        //debug::stopMessung("updateValues");    
        return $retVal;
    }
    
    /**
    *    Escapes values for the update- and insert querys
    *    Normally "addslashes", in case of an SQLite DBMS "sqlite_escape_string" is used
    *
    *    @param string $value, the unescaped value
    *    @return string the escaped value
    */
    public function escapeForDbQuery( $value ) {
        $escFunction = $this->escapingFunction;
        $serverType = substr( $this->dsn, 0, strpos($this->dsn, ":") );
        
        if ( isset( $this->specEscFunction[ $serverType ] ) ) {
            $escFunction = $this->specEscFunction[ $serverType ];
        }
        
        return $escFunction($value);
    }
    
    /**
    *    Deletes all records from a table that fulfill the given condition
    *
    *    @param string $tablename, the name of the table to delete records from
    *    @param string $condition, coundtion the records must fulfill to be deleted (e.g. "id=12")
    *
    *    @return int the number of updated records
    *        boolean false on error
    *
    *    NOTES:
    *        - use "===" to check for failures, integer 0 would mean: no SQL-Error, but no record found 
    *            that fulfills the given condition, and so nothing deleted
    *        - if the condition is empty nothing will be done to avoid that you update a whole 
    *            table to the same values by a mistake. If you want to do so give something like
    *            "true" or "1 = 1" as condition
    */
    function delete( $table, $cond, $noQuote = array() ) {
        if ( is_array($cond) ) {
            foreach ( $cond as $col => $value ) {
                $value = $this->escapeForDbQuery( $value );
                if ( array_search( $col, $noQuote ) === false ) {
                    $c[] = "$col='$value'";
                } else {
                    $c[] = "$col=$value";
                }
            }
            $cond = implode( " AND ", $c );
        }
        $affectedRows = false;
        $sql = "DELETE FROM $table WHERE $cond";
        //echo $sql;
        if ($cond != "") $affectedRows = $this->execute( $sql );
        return affectedRows;
    }

    /**
    *    Sets if on errors a debug-message should be printed to the standard output
    *    The default is true, set it to false in a productional environment.
    *
    *    @param boolen $debug, show errors or not.
    */
    function showErrors($debug) {
        $this->debugMode = $debug;
    }
    
    
    
    //    INTERNA
    // ===================================
    
    // Führt einen Select SQL aus, gibt das resultat numerisch oder assoziative zurück
    //    für return_mode die PDO::FETCH_xxx Konstanten verwenden

    /**
    *    The internal function to perform SELECT-Querys
    *
    *    @param string $sql, the SQL to perform
    *    @return_mode the return mode according to the PDO->fetchAll function
    *    
    *    @return array an array of the results;
    */
    private function select($sql, $return_mode) {
        $result = false;
        if (is_object($this->db)) {
            //debug::startMessung("SQL: ".$sql);        
            $result = array();        
            $statement = $this->db->query($sql);
            if ($statement === false) {
                if ($this->handleError($sql) == true) {    
                    $statement = $this->db->query($sql);                
                }
            }

            if ( $statement === false ) {
                $this->showError( $sql );
            }
            else {
                $result = $statement->fetchAll($return_mode);
            }
            //debug::stopMessung("SQL: ".$sql);
        }
        return $result;
        
    }

    /**
    *    Internal insrting function 
    *    
    *    @param $sql, the SQL-INSERT Statement
    *    
    *    @return int, the newly created id if an auto_increment col is in the table
    *        int, number of inserted rows if no auto_increment col
    *        boolean false on error
    */
    private function insert( $sql, $tablename="" ) {
        $retVal = $this->db->exec( $sql );
        if ( $retVal === false ) {
            if ( $this->handleError( $sql ) == true ) {
                $retVal = $this->db->exec( $sql );
            }
        }
        
        if ( $retVal === false ) {
            $this->showError( $sql );
        }
        else {
            if ( $this->db->lastInsertId() > 0 ) {
                $retVal = $this->db->lastInsertId();
            }
        }
        
        return $retVal;
    }
    
    /**
    *    Internal SQL-Execution of UPDATE and DELETE-Statements
    *    
    *    @param $sql, the SQL Statement
    *    
    *    @return int, number of affected rows (updated oder deleted)
    *        boolean false on error
    */
    public function execute( $sql ) {
        $affectedRows = $this->db->exec($sql);
        if ( $affectedRows === false ) {
            if ($this->handleError( $sql ) == true) {
                $affectedRows = $this->db->exec($sql);                
            }
        }
        
        if ( $affectedRows === false ) {
            $this->showError( $sql );
        }
        
        return $affectedRows;
    }
    
    /**
    *    Internal Error handling 
    *    Tries to reconnect on a failureCode HY000
    *
    *    @return if the SQL-Call should be tried again (i.e. a reconnect has been done)
    */
    private function handleError( $sql ) {
        $this->showError( $sql );
        
        $rawError = $this->db->errorInfo();
        $retry = false;        
        if ( $rawError[0] == "HY000" ) {    
            // Dieser Fehlercode bedeutet dass dir Verbindung neu aufgebaut werden muss. 
            // Und zwar ohne persistent!
            // Problem PDO behaltet die Verbindungen sonst ewig (länger als 8h was mehr als der wait_timeout der DbServer ist)

            if ($this->debugMode) print("Try to reconnect...");
            
            $this->db = new PDO($this->dsn, $this->user, $this->pw);            
            //$this->db = new PDO($this->dsn, $this->user, $this->pw, array(PDO::ATTR_PERSISTENT => true));
            
            $retry = true;
        }

        return $retry;
    }
    
    /**
    *    shows a niocely formatted erroe message if an SQL-Statement fails if debugMode is true (default)
    *    @uses debug::show 
    *    @see stdfuncs.inc.php
    *    
    *    @param string $sql, the SQL that failed.
    */
    private function showError($sql) {
        if ( $this->debugMode ) {
            $rawError = $this->db->errorInfo();
            $error['code'] = $rawError[0]." / ".$rawError[1];
            $error['sql'] = $sql;
            $error['message'] = $rawError[2];
            
            debug::show( $error, "SQL-Fehler" );
        }
    }


    //    HILFS-FUNKTION    
    // ===================================
    
    /**
    *    Creates from a user entered european date (i.e. "dd.mm.yyyy") an SQL-Date
    *    using wildcards (%)
    *
    *    e.g.     %.%.2007 will get 2007-%-%
    *        10.%.2007 will get 2007-%-10
    *        10.2007 will get 2007-10-% NOTE the leading dot
    *        10.8 will get 10-08-2007 (in the year 2007 !)     
    *        10.8.2007 gets 2007-08-10
    *    
    *    @param string $euroDate the in eurpean manner entered date
    *    
    *    @return string the wildcarded SQL-formatted date
    */
    function createWildCardedSQLDate($euroDate) {
        $euroDate = trim($euroDate);
        if (substr($euroDate, strlen($euroDate) - 1) == "%") {
            $euroDate = substr($euroDate, 0, strlen($euroDate)-1);
        }
        if ($euroDate == "") {
            $sqlFilterDate = "%";
        }
        else {
    
            $ed = explode(".", $euroDate);
            if (count($ed) == 1) $ed[1] = date("m");    // Fehlenden Monat durach Aktuellen Monat ersetzen
            if (count($ed) == 2) $ed[2] = date("Y");    // Fehlendes Jahr durch aktuelles Ersetzen

            if ($ed[0] == "") $ed[0] = "%";            // Leerstring als Wildcard interpretieren
            if ($ed[1] == "") $ed[1] = "%";    
            if ($ed[2] == "") $ed[2] = "%";    
            if (strlen($ed[0]) == 1 && $ed[0] != "%") $ed[0] = "0".$ed[0];  // Falls Tag = [0..9] : zweistellig machen 
            if (strlen($ed[1]) == 1 && $ed[1] != "%") $ed[1] = "0".$ed[1];  // Falls Monat = [0..9] : zweistellig machen 

            if (strlen($ed[2]) == 1 && $ed[2] != "%") $ed[2] = substr(date('Y'), 0, 3).$ed[2];    // Jahr vierstellig machen
            if (strlen($ed[2]) == 2) $ed[2] = substr(date('Y'), 0, 2).$ed[2];    // Jahr vierstellig machen
            if (strlen($ed[2]) == 3) $ed[2] = substr(date('Y'), 0, 1).$ed[2];    // Jahr vierstellig machen

            krsort($ed);
            $sqlFilterDate = join("-", $ed);
        }
        
        // print("Aus $euroDate => $sqlFilterDate<br>\n");
        return $sqlFilterDate;
    }
    

}

?>
